﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DbAdapter.sqldbAdapter;

namespace DAO.WaiGaoQiao
{
    /// <summary>
    /// 劳保调出
    /// </summary>
    public class DataAccessWork_OutStorage
    {
        /// <summary>
        /// 保存劳保物品调出
        /// </summary>
        /// <param name="idpara"></param>
        /// <returns></returns>
        public int SaveOutStorageDetailed(object[] idpara)
        {
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(GetConntionString.conntionstring(), "Proc_WorkOutStorageDetailed", idpara);
        }

        /// <summary>
        /// 获取劳保物品调出明细总数
        /// </summary>
        /// <returns></returns>
        public int GetCountOutStorageDetailed(string condition)
        {
            string query = "select count(1) from Work_OutStorage_Detailed where 1=1";
            if (!string.IsNullOrEmpty(condition))
            {
                query += condition;
            }
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }
        /// <summary>
        /// 根据条件获取劳保物品调出（不分页）
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataSet GetAllOutStorageDetailed(string condition)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select * FROM Work_OutStorage_Detailed Where 1=1 " + condition;
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }
        /// <summary>
        /// 获取劳保物品调出分页
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="pagesize"></param>
        /// <param name="startindex"></param>
        /// <returns></returns>
        public DataSet GetAllOutStorageDetailed(string condition, int pagesize, int startindex)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                int startrow = pagesize * (startindex - 1);
                int endrow = pagesize * startindex + 1;
                string query = "SELECT B.ID,B.OutDt,B.Article_Id,B.Article_Name,B.Article_NO,"
                    + "B.Article_Unit,B.Count,B.Operate,B.Remark,B.RecordDateTime,B.OrderNo "
                    + "FROM (select ROW_NUMBER() OVER(ORDER BY A.ID DESC) rownumber,ID ,OutDt ,Article_Id,"
                    + " Article_Name,Article_No,Article_Unit,Count,Operate,Remark,RecordDateTime,OrderNo"
                    + " from Work_OutStorage_Detailed A Where 1=1 " + condition + ") B "
                    + " where B.rownumber > " + startrow.ToString() + " and B.rownumber < " + endrow.ToString();
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }
        /// <summary>
        /// 根据条件删除劳保物品调出
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int DeleteOutStorageDetailed(string condition)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "Delete from Work_OutStorage_Detailed where " + condition;
                return db.ExecuteNonQuery(conn, CommandType.Text, query);
            }
        }

      

        /// <summary>
        /// 获取劳保物品调出批号
        /// </summary>
        /// <returns></returns>
        public string GetOutStorageDetailedOrderNo()
        {
            sqldb db = new sqldb();
            string query = "Execute GetWork_OutStorage_Detailed_OrderNo";
            object strorderno = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return strorderno.ToString();
        }
        /// <summary>
        /// 严重出库数量
        /// </summary>
        /// <param name="article_id"></param>
        /// <param name="article_unit"></param>
        /// <returns></returns>
        public int ValidateOutStorage(string article_id,string article_unit)
        {
            sqldb db = new sqldb();
            StringBuilder sb = new StringBuilder();
            sb.Append("select (select isnull(sum([count]),0) as AllCount from [Work_Storage_Detailed]");
            sb.AppendFormat("where article_id='{0}' and article_unit='{1}')-",article_id,article_unit);
            sb.Append("(select  isnull(sum([count]),0) as UseCount from ");
            sb.AppendFormat("dbo.Work_OutStorage_Detailed where article_id='{0}' and article_unit='{1}') as Remaincount", article_id, article_unit);
            object  rtnCount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, sb.ToString());
            return Convert.ToInt32(rtnCount) ;
        }
    }
}
